package com.kehongborui.empmanagesys.dao;

import com.kehongborui.empmanagesys.entity.Emp;
import com.kehongborui.empmanagesys.model.QueryCondition;
import com.kehongborui.empmanagesys.util.CommonUtil;
import com.kehongborui.empmanagesys.util.DbUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author zhang peng yang ming
 * @CopyRight (C) http://www.csdn.com
 * @brief
 * @email 122162005@qq.com
 * @date 2018-07-18
 */
public class EmpDao {
    public Integer amount(QueryCondition queryCondition) {
        return null;
    }

    public List<Emp> findAll(QueryCondition queryCondition) {
        return null;
    }
    public List<Emp> findAll() {
        List<Emp> empList = new ArrayList<>();
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = connection.prepareStatement("select * from khbr_emp");
            rs = statement.executeQuery();

            while (rs.next()) {
                Emp tmpEmp = new Emp();
                tmpEmp.setIdCardNo(rs.getString("idcard_no"));
                tmpEmp.setName(rs.getString("name"));
                tmpEmp.setTel(rs.getString("tel"));
                tmpEmp.setId(rs.getInt("id"));
                tmpEmp.setBirth(rs.getTimestamp("birth"));
                tmpEmp.setHiredate(rs.getTimestamp("hiredate"));
                tmpEmp.setLeavedate(rs.getTimestamp("leavedate"));
                tmpEmp.setStatus(rs.getInt("status"));
                tmpEmp.setGender(rs.getInt("gender"));
                empList.add(tmpEmp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeConnection(connection);
            DbUtil.closeRsStmt(rs, statement);
        }

        return empList;
    }

    /**
     * 根据员工id获取员工信息
     * @param id
     * @return
     */
    public Emp findById(int id) {
        Emp tmpEmp = new Emp();
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            statement = connection.prepareStatement("select * from khbr_emp where id=?");
            statement.setInt(1, id);
            rs = statement.executeQuery();

            if (rs.next()) {
                tmpEmp.setIdCardNo(rs.getString("idcard_no"));
                tmpEmp.setName(rs.getString("name"));
                tmpEmp.setTel(rs.getString("tel"));
                tmpEmp.setId(rs.getInt("id"));
                tmpEmp.setBirth(rs.getTimestamp("birth"));
                tmpEmp.setHiredate(rs.getTimestamp("hiredate"));
                tmpEmp.setLeavedate(rs.getTimestamp("leavedate"));
                tmpEmp.setStatus(rs.getInt("status"));
                tmpEmp.setGender(rs.getInt("gender"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeConnection(connection);
            DbUtil.closeRsStmt(rs, statement);
        }

        return tmpEmp;
    }

    /**
     * 根据员工id 修改员工信息
     *
     * @param emp
     * @return
     */
    public boolean updateById(Emp emp) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        int rs = 0;
        try {
            statement = connection.prepareStatement("update khbr_emp set leavedate=?, gender=? where id=?");
            statement.setTimestamp(1, emp.getLeavedate());
            statement.setInt(2, emp.getGender());
            statement.setInt(3, emp.getId());
            rs = statement.executeUpdate();


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DbUtil.closeConnection(connection);
        }

        return rs > 0;
    }

    /**
     * 根据id 删除员工信息
     * @param id
     * @return
     */
    public boolean deleteById(Integer id) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        int rs = 0;
        try {
            statement = connection.prepareStatement("delete from khbr_emp  where id=?");
            statement.setInt(1, id);
            rs = statement.executeUpdate();


        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DbUtil.closeConnection(connection);
        }

        return rs > 0;
    }

    /**
     * 根据员工id 修改员工信息
     *
     * @param emp
     * @return
     */
    public boolean save(Emp emp) {
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        int rs = 0;
        try {
            statement = connection.prepareStatement("insert into khbr_emp " +
                    "(name, idcard_no, gender, birth, hiredate, leavedate, status) " +
                    "values (?, ?, ?, ?, ?, ?, ?)");

            statement.setString(1, emp.getName());
            statement.setString(2, emp.getIdCardNo());
            statement.setInt(3, emp.getGender());
            statement.setTimestamp(4, emp.getBirth());
            statement.setTimestamp(5, emp.getHiredate());
            statement.setTimestamp(6, emp.getLeavedate());
            statement.setInt(7, emp.getStatus());
            rs = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            DbUtil.closeConnection(connection);
        }

        return rs > 0;
    }

    public List<Emp> query(QueryCondition condition) {
        List<Emp> empList = new ArrayList<>();
        Connection connection = DbUtil.getConnection();
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            StringBuffer sql = new StringBuffer("select * from khbr_emp WHERE 1 = 1");

            // 拼接姓名
            if (condition.getAge() != null) {
                sql.append(" and age = " + condition.getAge());
            }// 拼接姓名
            if (condition.getGender() != null) {
                sql.append(" and gender = " + condition.getGender());
            }// 拼接姓名
            if (condition.getName() != null) {
                sql.append(" and name = '" + condition.getName() + "'");
            }// 拼接姓名
            if (condition.getSalMin() != null) {
                sql.append(" and sal > " + condition.getSalMin());
            }// 拼接姓名
            if (condition.getSalMax() != null) {
                sql.append(" and sal < " + condition.getSalMax());
            }
            statement = connection.prepareStatement(sql.toString());
            rs = statement.executeQuery();

            while (rs.next()) {
                Emp tmpEmp = new Emp();
                tmpEmp.setIdCardNo(rs.getString("idcard_no"));
                tmpEmp.setName(rs.getString("name"));
                tmpEmp.setTel(rs.getString("tel"));
                tmpEmp.setId(rs.getInt("id"));
                tmpEmp.setBirth(rs.getTimestamp("birth"));
                tmpEmp.setHiredate(rs.getTimestamp("hiredate"));
                tmpEmp.setLeavedate(rs.getTimestamp("leavedate"));
                tmpEmp.setStatus(rs.getInt("status"));
                tmpEmp.setGender(rs.getInt("gender"));
                empList.add(tmpEmp);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbUtil.closeConnection(connection);
            DbUtil.closeRsStmt(rs, statement);
        }

        return empList;
    }
}
